﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Bll;
using Entity;
using Utils;
using Analysis.Models.EchartModel;
using Entity.BBC;
using System.Collections.ObjectModel;

namespace Analysis.Controllers
{
    public class AnalysisController : Controller
    {

        private static Dictionary<int, string> orderPlateformMap = new Dictionary<int, string>();
        private static Dictionary<int, string> userPlateformMap = new Dictionary<int, string>();
        private static Dictionary<int, string> ageMap = new Dictionary<int, string>();
        private static Dictionary<int, string> sexMap = new Dictionary<int, string>();

        private static double chargeRateA = 0.06;
        private static double chargeRateB = 0.02;
        private static int runNum = 0;

        public AnalysisController()
        {
            if (runNum == 0)
            {
                Init();
                runNum = 1;
            }
        }

        private void Init()
        {
            orderPlateformMap.Add(0, "BBC商城");
            orderPlateformMap.Add(1, "微商城");
            orderPlateformMap.Add(2, "小程序");
            orderPlateformMap.Add(3, "趣你加");

            //0：商城; 1：微商城; 2：App; 3：小程序; 4：投票; 5：扫码
            userPlateformMap.Add(0, "BBC商城");
            userPlateformMap.Add(1, "微商城");
            userPlateformMap.Add(2, "APP");
            userPlateformMap.Add(3, "小程序");
            userPlateformMap.Add(4, "投票");
            userPlateformMap.Add(5, "趣你加");

            sexMap.Add(0, "女");
            sexMap.Add(1, "男");
            sexMap.Add(2, "保密");

            for (int i = 0; i <= 120; i++)
            {
                if (i <= 10)
                {
                    ageMap.Add(i, "0-10岁");
                }
                else if (i > 10 && i <= 20)
                {
                    ageMap.Add(i, "11-20岁");
                }
                else if (i > 20 && i <= 30)
                {
                    ageMap.Add(i, "21-30岁");
                }
                else if (i > 30 && i <= 40)
                {
                    ageMap.Add(i, "31-40岁");
                }
                else if (i > 40 && i <= 50)
                {
                    ageMap.Add(i, "41-50岁");
                }
                else if (i > 50 && i <= 60)
                {
                    ageMap.Add(i, "51-60岁");
                }
                else if (i > 60 && i <= 70)
                {
                    ageMap.Add(i, "61-70岁");
                }
                else if (i > 70 && i <= 80)
                {
                    ageMap.Add(i, "71-80岁");
                }
                else if (i > 80 && i <= 90)
                {
                    ageMap.Add(i, "81-90岁");
                }
                else if (i > 90 && i <= 100)
                {
                    ageMap.Add(i, "91-100岁");
                }
                else
                {
                    ageMap.Add(i, "百岁老人");
                }
            }
        }

        public ActionResult Index()
        {

            List<ProductCategory> productCategoryList = BllBBCCommon.SelectEntityBySql<ProductCategory>("ProductCategory", "select * from ProudctCategroy where fatherId=0 and status=1");

            ViewBag.productCategoryList = productCategoryList;
            return View();
        }


        #region 商品销售额排行

        public JsonResult ProductSaleSort(int plateform, long category, int topNum)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            string sql = "select top " + topNum + " p.productName,sum(factPrice) as total from orders as o,orderdetail as od,product as p where od.orderId=o.id and o.orderstatusId=5 and o.orderNum not in(select orderNum from GoodsReturnOrder where [Status]!=5) and p.id=od.productId and dbo.f_isBelongToFather(" + category + ",p.categoryId)=1 ";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += "and p.displayUnits!=1 ";
                }
                else if (plateform == 2)
                {
                    sql += "and p.displayUnits!=3 ";
                }
            }
            sql += " group by  p.productName order by total desc";

            List<SelectForProductSaleSort> objList = BllBBCCommon.SelectEntityBySql<SelectForProductSaleSort>("orders", sql);

            string sqlTotal = "select sum(o.realTotal) as totalIncome from orders as o where o.orderstatusId=5 and o.orderNum not in(select orderNum from GoodsReturnOrder where [Status]!=5)";

            decimal totalIncome = BllBBCCommon.SelectSingleValueBySql("orders", sqlTotal, "decimal").ConToDecimal();

            List<string> legendList = new List<string>();
            legendList.Add("销售额");
            legendList.Add("占总销售额百分比");
            List<string> productNameList = new List<string>();
            List<decimal> totalList = new List<decimal>();
            List<decimal> rateList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();


            foreach (SelectForProductSaleSort obj in objList)
            {
                string productName = obj.productName;
                decimal total = obj.total;

                productNameList.Add(productName);
                totalList.Add(total);

                decimal rate = 0;

                if (totalIncome > 0)
                {
                    rate = Math.Round(total / totalIncome, 4);
                }

                
                rate = rate * 100;
                rateList.Add(rate);
            }
            dataList.Add(totalList);
            dataList.Add(rateList);


            BarOrLineModel bar = new BarOrLineModel();
            bar.NameList = productNameList;
            bar.DataList = dataList;
            bar.LegendList = legendList;

            result.Status = 200;
            result.Body = bar;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectForProductSaleSort
        {
            public string productName { get; set; }

            public decimal total { get; set; }
        }
        #endregion

        #region 区域销售额排行

        public JsonResult ProductAreaSort(int plateform, int topNum)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            string sql = "select top " + topNum + " c.cityName,d.districtName,sum(realTotal) as total from orders as o,S_District as d,S_City as c where o.areaId=d.districtId and d.cityId=c.cityId and o.orderStatusId=5 ";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += "and o.plateform<3 ";
                }
                else if (plateform == 2)
                {
                    sql += "and o.plateform=3 ";
                }
            }
            sql += " group by c.cityName,d.districtName order by total desc";


            List<SelectForProductAreaSort> objList = BllBBCCommon.SelectEntityBySql<SelectForProductAreaSort>("orders", sql);

            string sqlTotal = "select sum(o.realTotal) as totalIncome from orders as o where o.orderstatusId=5 and o.orderNum not in(select orderNum from GoodsReturnOrder where [Status]!=5)";

            decimal totalIncome = BllBBCCommon.SelectSingleValueBySql("orders", sqlTotal, "decimal").ConToDecimal();

            List<string> legendList = new List<string>();
            legendList.Add("销售额");
            legendList.Add("占总销售额百分比");

            List<string> nameList = new List<string>();
            List<decimal> totalList = new List<decimal>();
            List<decimal> rateList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            foreach (SelectForProductAreaSort obj in objList)
            {
                string cityName = obj.cityName;
                string districtName = obj.districtName;
                decimal total = obj.total;

                nameList.Add(cityName + districtName);
                totalList.Add(total);
                decimal rate = 0;
                if (totalIncome > 0)
                {
                    rate = Math.Round(total / totalIncome, 4) * 100;
                }
                rateList.Add(rate);
            }
            dataList.Add(totalList);
            dataList.Add(rateList);

            BarOrLineModel bar = new BarOrLineModel();
            bar.NameList = nameList;
            bar.LegendList = legendList;
            bar.DataList = dataList;

            result.Status = 200;
            result.Body = bar;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectForProductAreaSort
        {
            public string cityName { get; set; }
            public string districtName { get; set; }
            public decimal total { get; set; }
        }

        #endregion

        #region 产品销售利润分析
        public JsonResult ProductPropertySale(int plateform)
        {
            Result<ScatterModel> result = new Result<ScatterModel>();

            List<string> legendList = new List<string>();
            List<List<List<double>>> dataList = new List<List<List<double>>>();

            string sql = "select p.productName,COALESCE(sum(od.factPrice*od.amount),0) as productSaleAmount,COALESCE(sum(od.costPirce*od.amount),0) as productCostAmount from product as p, orderDetail as od,orders as o where o.id=od.orderId and od.productId=p.id and o.orderStatusId=5 and o.orderNum not in(select orderNum from GoodsReturnOrder where [Status]!=5)";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += " and o.plateform<3 ";
                }
                else if (plateform == 2)
                {
                    sql += " and o.plateform=3 ";
                }
            }
            sql += " group by p.productName";

            List<SelectForProductPropertySale> objList = BllBBCCommon.SelectEntityBySql<SelectForProductPropertySale>("orders", sql);


            foreach (SelectForProductPropertySale obj in objList)
            {
                string productName = obj.productName;

                double productSaleAmount = obj.productSaleAmount.ConToDouble();
                double productCostAmount = obj.productCostAmount.ConToDouble();

                double productPropertyAmount = productSaleAmount - productCostAmount - productSaleAmount * 0.3;

                legendList.Add(productName);

                List<double> temp = new List<double>();
                temp.Add(productSaleAmount);
                temp.Add(productPropertyAmount);

                List<List<double>> temp2 = new List<List<double>>();
                temp2.Add(temp);

                dataList.Add(temp2);
            }


            ScatterModel scatterModel = new ScatterModel();
            scatterModel.DataList = dataList;
            scatterModel.LegendList = legendList;

            result.Status = 200;
            result.Body = scatterModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectForProductPropertySale
        {
            public string productName { get; set; }

            public decimal productSaleAmount { get; set; }

            public decimal productCostAmount { get; set; }
        }

        #endregion

        #region 区域销售额分析
        public JsonResult ProductAreaSale(int plateform)
        {
            Result<EMapModel> result = new Result<EMapModel>();

            decimal min = 0;
            decimal max = 0;
            List<string> legendList = new List<string>();
            legendList.Add("销售额");
            List<List<EMapDataModel>> dataList = new List<List<EMapDataModel>>();
            List<EMapDataModel> dataModelList = new List<EMapDataModel>();

            string sql = "select p.provinceId,sum(o.realTotal) as total from orders as o,S_Province as p where o.orderStatusId=5 and orderNum not in (select orderNum from GoodsReturnOrder where [Status]!=5) and o.provinceId=p.provinceId";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += " and o.plateform<3 ";
                }
                else if (plateform == 2)
                {
                    sql += " and o.plateform=3 ";
                }
            }
            sql += " group by p.provinceId";

            List<SelectForProductAreaSale> objList = BllBBCCommon.SelectEntityBySql<SelectForProductAreaSale>("orders", sql);


            List<SProvince> provinceList = BllBBCCommon.SelectEntityBySql<SProvince>("SProvince", "select * from s_province");


            foreach (SProvince province in provinceList)
            {
                bool flag = false;
                string provinceName = province.ProvinceName;
                if (provinceName.Contains("内蒙古") || provinceName.Contains("黑龙江"))
                {
                    provinceName = provinceName.Substring(0, 3);
                }
                else
                {
                    provinceName = provinceName.Substring(0, 2);
                }
                long provinceId = province.ProvinceID;
                foreach (SelectForProductAreaSale obj in objList)
                {
                    long provinceIdTemp = obj.provinceId;
                    if (provinceId == provinceIdTemp)
                    {
                        decimal total = obj.total;

                        EMapDataModel model = new EMapDataModel();
                        model.name = provinceName;
                        model.value = total;

                        dataModelList.Add(model);
                        flag = true;
                    }
                }
                if (!flag)
                {
                    EMapDataModel model = new EMapDataModel();
                    model.name = provinceName;
                    model.value = 0;

                    dataModelList.Add(model);
                }
            }
            dataList.Add(dataModelList);

            foreach (List<EMapDataModel> tempList in dataList)
            {
                foreach (EMapDataModel model in tempList)
                {
                    decimal total = model.value;
                    if (min > total)
                    {
                        min = total;
                    }
                    if (max < total)
                    {
                        max = total;
                    }
                }
            }

            EMapModel mapModel = new EMapModel();
            mapModel.MapType = "china";
            mapModel.DataList = dataList;
            mapModel.LegendList = legendList;
            mapModel.Max = max;
            mapModel.Min = min;


            result.Status = 200;
            result.Body = mapModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectForProductAreaSale
        {
            public long provinceId { get; set; }
            public decimal total { get; set; }
        }
        #endregion


        #region 退货产品分析
        public JsonResult ProductRetrunGood(int plateform, long category, int topNum)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            string sql = "select top " + topNum + " p.productName,sum(factPrice) as total from orders as o,orderdetail as od,product as p where od.orderId=o.id and o.orderstatusId=5 and o.orderNum in(select orderNum from GoodsReturnOrder where [Status]!=5) and p.id=od.productId and dbo.f_isBelongToFather(" + category + ",p.categoryId)=1 ";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += "and p.displayUnits!=1 ";
                }
                else if (plateform == 2)
                {
                    sql += "and p.displayUnits!=3 ";
                }
            }
            sql += "group by  p.productName order by total desc";

            List<SelectProductRetrunGood> objList = BllBBCCommon.SelectEntityBySql<SelectProductRetrunGood>("orders", sql); ;

            string sqlTotal = "select sum(o.realTotal) as totalIncome from orders as o where o.orderstatusId=5 and o.orderNum in(select orderNum from GoodsReturnOrder where [Status]!=5)";

            decimal totalIncome = BllBBCCommon.SelectSingleValueBySql("orders", sqlTotal, "decimal").ConToDecimal();

            List<string> legendList = new List<string>();
            legendList.Add("退款额");
            legendList.Add("占总退款额百分比");

            List<string> productNameList = new List<string>();
            List<decimal> totalList = new List<decimal>();
            List<decimal> rateList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            foreach (SelectProductRetrunGood obj in objList)
            {
                string productName = obj.productName;
                decimal total = obj.total;

                productNameList.Add(productName);
                totalList.Add(total);

                decimal rate = 0;
                if (totalIncome > 0)
                {
                    rate = Math.Round(total / totalIncome, 4) * 100;
                }

                rateList.Add(rate);
            }
            dataList.Add(totalList);
            dataList.Add(rateList);

            BarOrLineModel bar = new BarOrLineModel();
            bar.NameList = productNameList;
            bar.LegendList = legendList;
            bar.DataList = dataList;

            result.Status = 200;
            result.Body = bar;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectProductRetrunGood
        {
            public string productName { get; set; }
            public decimal total { get; set; }
        }

        #endregion

        #region 平台用户统计
        public JsonResult UserInfoFromPlateform()
        {
            Result<PieModel> result = new Result<PieModel>();

            List<string> legendList = new List<string>();
            List<PieDataModel> dataList1 = new List<PieDataModel>();
            List<List<PieDataModel>> dataList = new List<List<PieDataModel>>();

            string sql = "select plateform,count(*) as total from userInfo where plateform is not null  group by plateform";

            List<SelectUserInfoFromPlateform> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoFromPlateform>("userInfo", sql); ;

            foreach (SelectUserInfoFromPlateform obj in objList)
            {
                string plateform = userPlateformMap[obj.plateform];
                int temp = obj.total;
                decimal value = new decimal(temp);

                PieDataModel model = new PieDataModel();
                model.name = plateform;
                model.value = value;

                dataList1.Add(model);
                legendList.Add(plateform);
            }
            dataList.Add(dataList1);

            PieModel pieModel = new PieModel();
            pieModel.DataList = dataList;
            pieModel.LegendList = legendList;

            result.Status = 200;
            result.Body = pieModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }
        private class SelectUserInfoFromPlateform
        {
            public int plateform { get; set; }
            public int total { get; set; }
        }
        #endregion

        #region 用户增长率
        public JsonResult UserInfoIncreaseTendency(int plateform, int timeType)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            List<string> legendList = new List<string>();
            legendList.Add("用户量");
            legendList.Add("增长率");

            List<string> nameList = new List<string>();
            List<decimal> userInfoAmountList = new List<decimal>();
            List<decimal> userInfoIncreaseList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            string sql = "exec userInfoIncreaseTendency @plateform=" + plateform + ",@timeType=" + timeType;

            List<SelectUserInfoIncreaseTendency> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoIncreaseTendency>("userInfoIncreaseTendency", sql);
            foreach (SelectUserInfoIncreaseTendency obj in objList)
            {
                string name = obj.timestring;
                nameList.Add(name.Replace("-00", ""));
                decimal userInfoAmount = obj.userInfoAmount;
                userInfoAmountList.Add(userInfoAmount);
                decimal userInfoIncrease = obj.userInfoIncrease;
                userInfoIncreaseList.Add(userInfoIncrease);
            }
            dataList.Add(userInfoAmountList);
            dataList.Add(userInfoIncreaseList);

            BarOrLineModel barOrLineModel = new BarOrLineModel();
            barOrLineModel.DataList = dataList;
            barOrLineModel.NameList = nameList;
            barOrLineModel.LegendList = legendList;

            result.Status = 200;
            result.Body = barOrLineModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }
        private class SelectUserInfoIncreaseTendency
        {
            public string timestring { get; set; }

            public int userInfoAmount { get; set; }

            public decimal userInfoIncrease { get; set; }
        }
        #endregion

        #region 活跃用户统计
        public JsonResult UserInfoActivityAnalysis(string beginTime, string endTime, string amount)
        {
            Result<PieModel> result = new Result<PieModel>();

            List<string> legendList = new List<string>();
            legendList.Add("登录用户");
            legendList.Add("系统死粉");
            legendList.Add("登录活跃用户");
            legendList.Add("登录非活跃用户");

            List<string> seriesNameList = new List<string>();
            seriesNameList.Add("登录用户比");
            seriesNameList.Add("登录活跃用户比");

            List<PieDataModel> dataList1 = new List<PieDataModel>();
            List<PieDataModel> dataList2 = new List<PieDataModel>();


            if (beginTime == "" || beginTime == null)
            {
                beginTime = "2000-01-01 00:00:00";
            }
            if (endTime == "" || endTime == null)
            {
                endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }

            //活动期间登录的用户数
            string sqlForLogin = "select count(distinct(userName)) as total from UserLoginRecord where createTime>='" + beginTime + "' and createTime<='" + endTime + "'";

            int loginUser = BllBBCCommon.SelectSingleValueBySql("UserLoginRecord", sqlForLogin, "int").ConToInt();

            //总用户数
            string sqlForTotal = "select count(*) as total from userInfo";

            int totalUser = BllBBCCommon.SelectSingleValueBySql("userInfo", sqlForTotal, "int").ConToInt();

            //购买金额超过amount的用户数
            string sqlForBuy = "with #tableForGetActivityUserCount as (select ur.userName,sum(o.realTotal) as totalConsumption from orders as o,userInfo as u,userLoginRecord as ur where u.userName=ur.userName and o.userId=u.id and o.createTime>='" + beginTime + "' and o.createTime<='" + endTime + "' group by ur.userName having sum(o.realTotal)>" + amount + ") select count(*) as total from #tableForGetActivityUserCount";

            int amountUser = BllBBCCommon.SelectSingleValueBySql("tableForGetActivityUserCount", sqlForBuy, "int").ConToInt();

            PieDataModel pieDataModel = new PieDataModel();
            pieDataModel.name = "登录用户";
            pieDataModel.value = loginUser;

            PieDataModel pieDataModel2 = new PieDataModel();
            pieDataModel2.name = "系统死粉";
            pieDataModel2.value = totalUser - loginUser;

            PieDataModel pieDataModel3 = new PieDataModel();
            pieDataModel3.name = "登录活跃用户";
            pieDataModel3.value = amountUser.ConToDecimal();

            PieDataModel pieDataModel4 = new PieDataModel();
            pieDataModel4.name = "登录非活跃用户";
            pieDataModel4.value = loginUser - amountUser;

            dataList1.Add(pieDataModel);
            dataList1.Add(pieDataModel2);

            dataList2.Add(pieDataModel3);
            dataList2.Add(pieDataModel4);


            List<List<PieDataModel>> dataList = new List<List<PieDataModel>>();
            dataList.Add(dataList1);
            dataList.Add(dataList2);

            PieModel pieModel = new PieModel();
            pieModel.LegendList = legendList;
            pieModel.DataList = dataList;
            pieModel.SeriesNameList = seriesNameList;


            result.Status = 200;
            result.Body = pieModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectUserInfoActivityAnalysis
        {

        }
        #endregion

        #region 用户年龄统计
        public JsonResult UserInfoAgeAnalysis(int plateform)
        {
            Result<PieModel> result = new Result<PieModel>();

            List<string> seriesNameList = new List<string>();
            seriesNameList.Add("填写年龄用户占比");
            seriesNameList.Add("各年龄段用户占比");
            List<string> legendList = new List<string>();
            List<PieDataModel> dataList1 = new List<PieDataModel>();
            List<PieDataModel> dataList2 = new List<PieDataModel>();

            List<List<PieDataModel>> dataList = new List<List<PieDataModel>>();

            string sqlForTotal = "select count(1) as userTotal from userInfo";
            int userTotal = BllBBCCommon.GetCountBySQL("userInfo", sqlForTotal);
            decimal userAgeTotal = 0;

            string sqlForAge = "select year(getdate())-year(birthday) as age,count(1) as total from userInfo where birthday is not null and userStatus=1";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sqlForAge += "and plateform<5";
                }
                else if (plateform == 2)
                {
                    sqlForAge += "and plateform=5";
                }
            }
            sqlForAge += " group by year(birthday)";

            List<SelectUserInfoAgeAnalysis> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoAgeAnalysis>("userInfo", sqlForAge); ;
            foreach (SelectUserInfoAgeAnalysis obj in objList)
            {
                int age = obj.age;
                string name = ageMap[age];
                decimal total = obj.total;
                userAgeTotal += total;

                if (!legendList.Contains(name))
                {
                    legendList.Add(name);

                    PieDataModel dataModel = new PieDataModel();
                    dataModel.name = name;
                    dataModel.value = total;

                    dataList2.Add(dataModel);
                }
                else
                {
                    for (int i = 0; i < legendList.Count(); i++)
                    {
                        if (legendList[i] == name)
                        {
                            PieDataModel temp = dataList2[i];
                            temp.value = temp.value + total;
                            dataList2[i] = temp;
                        }
                    }
                }
            }

            legendList.Add("未填年龄的用户");
            legendList.Add("填写年龄的用户");

            PieDataModel other = new PieDataModel();
            other.name = "未填年龄的用户";
            other.value = userTotal - userAgeTotal;
            PieDataModel other2 = new PieDataModel();
            other2.name = "填写年龄的用户";
            other2.value = userAgeTotal;
            dataList1.Add(other);
            dataList1.Add(other2);

            dataList.Add(dataList1);
            dataList.Add(dataList2);

            PieModel pieModel = new PieModel();
            pieModel.LegendList = legendList;
            pieModel.DataList = dataList;
            pieModel.SeriesNameList = seriesNameList;

            result.Status = 200;
            result.Body = pieModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }
        private class SelectUserInfoAgeAnalysis
        {
            public int age { get; set; }
            public int total { get; set; }
        }
        #endregion

        #region 用户性别统计
        public JsonResult UserInfoSexAnalysis(int plateform)
        {
            Result<PieModel> result = new Result<PieModel>();

            List<string> legendList = new List<string>();
            List<PieDataModel> dataList1 = new List<PieDataModel>();
            List<List<PieDataModel>> dataList = new List<List<PieDataModel>>();

            decimal userSexTotal = 0;
            string sqlForSex = "select sex,count(1) as total from userInfo where userStatus=1";

            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sqlForSex += "and plateform<5";
                }
                else if (plateform == 2)
                {
                    sqlForSex += "and plateform=5";
                }
            }
            sqlForSex += " group by  sex";

            List<SelectUserInfoSexAnalysis> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoSexAnalysis>("userInfo", sqlForSex); ;
            foreach (SelectUserInfoSexAnalysis obj in objList)
            {
                int sex = obj.sex;
                string name = sexMap[sex];
                int total = obj.total;
                userSexTotal += total;

                if (!legendList.Contains(name))
                {
                    legendList.Add(name);

                    PieDataModel dataModel = new PieDataModel();
                    dataModel.name = name;
                    dataModel.value = total;

                    dataList1.Add(dataModel);
                }
                else
                {
                    for (int i = 0; i < legendList.Count; i++)
                    {
                        if (legendList[i] == name)
                        {
                            PieDataModel temp = dataList1[i];
                            temp.value = temp.value + total;
                            dataList1[i] = temp;
                        }
                    }
                }
            }

            dataList.Add(dataList1);

            PieModel pieModel = new PieModel();
            pieModel.LegendList = legendList;
            pieModel.DataList = dataList;

            result.Status = 200;
            result.Body = pieModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectUserInfoSexAnalysis
        {
            public int sex { get; set; }

            public int total { get; set; }
        }
        #endregion

        #region 业务员增长率
        public JsonResult UserInfoSaleManIncreaseTendency(int timeType)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            List<string> legendList = new List<string>();
            legendList.Add("业务员");
            legendList.Add("增长率");

            List<string> nameList = new List<string>();
            List<decimal> saleManAmountList = new List<decimal>();
            List<decimal> saleManIncreaseList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            string sql = "exec saleManIncreaseTendency @timeType=" + timeType;

            List<SelectUserInfoSaleManIncreaseTendency> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoSaleManIncreaseTendency>("saleManIncreaseTendency", sql); ;
            foreach (SelectUserInfoSaleManIncreaseTendency obj in objList)
            {
                string name = obj.timestring;
                nameList.Add(name.Replace("-00", ""));
                int saleManAmount = obj.saleManAmount;
                saleManAmountList.Add(saleManAmount);
                decimal saleManIncrease = obj.saleManIncrease;
                saleManIncreaseList.Add(saleManIncrease);
            }
            dataList.Add(saleManAmountList);
            dataList.Add(saleManIncreaseList);

            BarOrLineModel barOrLineModel = new BarOrLineModel();
            barOrLineModel.DataList = dataList;
            barOrLineModel.NameList = nameList;
            barOrLineModel.LegendList = legendList;

            result.Status = 200;
            result.Body = barOrLineModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectUserInfoSaleManIncreaseTendency
        {
            public string timestring { get; set; }
            public int saleManAmount { get; set; }

            public decimal saleManIncrease { get; set; }
        }

        #endregion

        #region 业务员下挂会员排行
        public JsonResult UserInfoSaleManMemberSort()
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            List<string> legendList = new List<string>();
            legendList.Add("下挂会员数");
            List<string> nameList = new List<string>();
            List<decimal> dataList1 = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            string sqlForSaleMan = "exec saleManMemberSort";

            List<SelectUserInfoSaleManMemberSort> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoSaleManMemberSort>("saleManMemberSort", sqlForSaleMan); ;

            foreach (SelectUserInfoSaleManMemberSort obj in objList)
            {
                string name = obj.realName;
                int total = obj.total;
                nameList.Add(name);
                dataList1.Add(new decimal(total));
            }
            dataList.Add(dataList1);

            BarOrLineModel barOrLineModel = new BarOrLineModel();
            barOrLineModel.DataList = dataList;
            barOrLineModel.NameList = nameList;
            barOrLineModel.LegendList = legendList;

            result.Status = 200;
            result.Body = barOrLineModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectUserInfoSaleManMemberSort
        {
            public string realName { get; set; }
            public int total { get; set; }
        }

        #endregion

        #region 业务员收益排行
        public JsonResult UserInfoSaleManIncomeSort(string beginTime, string endTime)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            if (beginTime == "" || beginTime == null)
            {
                beginTime = "2000-01-01 00:00:00";
            }
            if (endTime == "" || endTime == null)
            {
                endTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            }

            List<string> legendList = new List<string>();
            legendList.Add("业务员收益");
            legendList.Add("直接销售额");
            legendList.Add("间接销售额");
            List<string> nameList = new List<string>();
            List<decimal> incomeList = new List<decimal>();
            List<decimal> saleDirectAmountList = new List<decimal>();
            List<decimal> saleInDirectAmountList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            string sqlForSaleMan = "exec SP_CaculateSaleManIncomeRealTime @beginTimeStr='" + beginTime + "',@endTimeStr='" + endTime + "',@saleManName=''";

            List<SelectUserInfoSaleManIncomeSort> objList = BllBBCCommon.SelectEntityBySql<SelectUserInfoSaleManIncomeSort>("SP_CaculateSaleManIncomeRealTime", sqlForSaleMan); ;

            List<SaleManIncomeOrderModel> modelList = new List<SaleManIncomeOrderModel>();

            foreach (SelectUserInfoSaleManIncomeSort obj in objList)
            {
                string realName = obj.realName;
                decimal saleDirectAmount = obj.saleDirectAmount;
                decimal saleInDirectAmount = obj.saleInDirectAmount;

                decimal income = GetIncome(saleDirectAmount, saleInDirectAmount, chargeRateA, chargeRateB);

                SaleManIncomeOrderModel model = new SaleManIncomeOrderModel();
                model.name = realName;
                model.income = income;
                model.saleDirectAmount = saleDirectAmount;
                model.saleInDirectAmount = saleInDirectAmount;

                modelList.Add(model);
            }

            modelList = modelList.OrderByDescending(x => x.income).ToList();

            foreach (SaleManIncomeOrderModel model in modelList)
            {
                saleDirectAmountList.Add(model.saleDirectAmount);
                saleInDirectAmountList.Add(model.saleInDirectAmount);
                incomeList.Add(model.income);
                nameList.Add(model.name);
            }

            dataList.Add(incomeList);
            dataList.Add(saleDirectAmountList);
            dataList.Add(saleInDirectAmountList);

            BarOrLineModel barOrLineModel = new BarOrLineModel();
            barOrLineModel.DataList = dataList;
            barOrLineModel.NameList = nameList;
            barOrLineModel.LegendList = legendList;

            result.Status = 200;
            result.Body = barOrLineModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SaleManIncomeOrderModel
        {
            public string name { get; set; }
            public decimal saleDirectAmount { get; set; }
            public decimal saleInDirectAmount { get; set; }
            public decimal income { get; set; }
        }

        private class SelectUserInfoSaleManIncomeSort
        {
            public string realName { get; set; }
            public decimal saleDirectAmount { get; set; }

            public decimal saleInDirectAmount { get; set; }
        }

        private decimal GetIncome(decimal saleDirectAmount, decimal saleInDirectAmount, double chargeRateA, double chargeRateB)
        {
            double temp = saleDirectAmount.ConToDouble() * chargeRateA;
            double temp2 = saleInDirectAmount.ConToDouble() * chargeRateB;
            decimal result = (temp + temp2).ConToDecimal();

            return result;
        }
        #endregion

        #region 平台收益统计
        public JsonResult OrderPlateformIncome()
        {
            Result<PieModel> result = new Result<PieModel>();

            List<string> legendList = new List<string>();
            List<PieDataModel> dataList1 = new List<PieDataModel>();
            List<List<PieDataModel>> dataList = new List<List<PieDataModel>>();

            string sql = "select plateform,sum(realTotal) as total from orders where orderStatusId=5 and orderNum not in(select orderNum from GoodsReturnOrder where [Status]!=5) group by plateform order by total desc ";

            List<SelectOrderPlateformIncome> objList = BllBBCCommon.SelectEntityBySql<SelectOrderPlateformIncome>("orders", sql); ;
            foreach (SelectOrderPlateformIncome obj in objList)
            {

                string name = orderPlateformMap[obj.plateform];
                decimal value = obj.total;

                legendList.Add(name);

                PieDataModel pieData = new PieDataModel();
                pieData.name = name;
                pieData.value = value;

                dataList1.Add(pieData);
            }

            dataList.Add(dataList1);

            PieModel pie = new PieModel();
            pie.LegendList = legendList;
            pie.DataList = dataList;

            result.Status = 200;
            result.Body = pie;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectOrderPlateformIncome
        {
            public int plateform { get; set; }
            public decimal total { get; set; }
        }
        #endregion

        #region 订单增长趋势
        public JsonResult OrderIncreaseTendency(int plateform, int timeType)
        {
            Result<BarOrLineModel> result = new Result<BarOrLineModel>();

            List<string> legendList = new List<string>();
            legendList.Add("销售额");
            legendList.Add("增长率");

            List<string> nameList = new List<string>();
            List<decimal> saleAmountList = new List<decimal>();
            List<decimal> saleIncreaseList = new List<decimal>();
            List<List<decimal>> dataList = new List<List<decimal>>();

            string sql = "exec orderIncreaseTendency @plateform=" + plateform + ",@timeType=" + timeType;

            List<SelectOrderIncreaseTendency> objList = BllBBCCommon.SelectEntityBySql<SelectOrderIncreaseTendency>("orderIncreaseTendency", sql); ;
            foreach (SelectOrderIncreaseTendency obj in objList)
            {
                string name = obj.timestring;
                nameList.Add(name.Replace("-00", ""));
                decimal saleAmount = obj.saleAmount;
                saleAmountList.Add(saleAmount);
                decimal saleIncrease = obj.saleIncrease;
                saleIncreaseList.Add(saleIncrease);
            }
            dataList.Add(saleAmountList);
            dataList.Add(saleIncreaseList);

            BarOrLineModel barOrLineModel = new BarOrLineModel();
            barOrLineModel.DataList = dataList;
            barOrLineModel.NameList = nameList;
            barOrLineModel.LegendList = legendList;

            result.Status = 200;
            result.Body = barOrLineModel;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectOrderIncreaseTendency
        {
            public string timestring { get; set; }
            public decimal saleAmount { get; set; }

            public decimal saleIncrease { get; set; }
        }
        #endregion

        #region 地区销售额
        public JsonResult GetCityData(string provinceName, int plateform)
        {
            Result<List<EMapDataModel>> result = new Result<List<EMapDataModel>>();

            string sqlForProvince = "select top 1 provinceId from S_Province where provinceName like '" + provinceName + "%'";

            long provinceId = BllBBCCommon.SelectSingleValueBySql("S_Province", sqlForProvince, "long").ConToLong();

            List<SCity> cityList = BllBBCCommon.SelectEntityBySql<SCity>("SCity", "select * from S_City where provinceId=" + provinceId);

            List<EMapDataModel> list = new List<EMapDataModel>();


            string sql = "select c.cityId,sum(o.realTotal) as total from orders as o,S_City as c where o.orderStatusId=5 and orderNum not in (select orderNum from GoodsReturnOrder where [Status]!=5) and o.cityId=c.cityId";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += " and o.plateform<3 ";
                }
                else if (plateform == 2)
                {
                    sql += " and o.plateform=3 ";
                }
            }
            sql += " group by c.cityId";

            List<SelectGetCityData> objList = BllBBCCommon.SelectEntityBySql<SelectGetCityData>("orders", sql); ;


            foreach (SCity city in cityList)
            {
                bool flag = false;
                string cityName = city.CityName;
                long cityIdTemp = city.CityID;
                foreach (SelectGetCityData obj in objList)
                {
                    long cityId = obj.cityId;
                    decimal value = obj.total;
                    if (cityIdTemp == cityId)
                    {
                        EMapDataModel model = new EMapDataModel();
                        model.name = cityName;
                        model.value = value;

                        list.Add(model);
                        flag = true;
                    }
                }
                if (!flag)
                {
                    EMapDataModel model = new EMapDataModel();
                    model.name = cityName;
                    model.value = 0;

                    list.Add(model);
                }
            }


            result.Status = 200;
            result.Body = list;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectGetCityData
        {
            public long cityId { get; set; }
            public decimal total { get; set; }
        }

        public JsonResult GetAreaData(string cityName, int plateform)
        {
            Result<List<EMapDataModel>> result = new Result<List<EMapDataModel>>();

            string sqlForCity = "select top 1 cityId from S_City where cityName like '" + cityName + "%'";

            long cityId = BllBBCCommon.SelectSingleValueBySql("S_City", sqlForCity, "long").ConToLong();

            List<SDistrict> districtList = BllBBCCommon.SelectEntityBySql<SDistrict>("SDistrict", "select * from S_District where cityId=" + cityId);

            string sql = "select d.districtId,sum(o.realTotal) as total from orders as o,S_District as d where o.orderStatusId=5 and orderNum not in (select orderNum from GoodsReturnOrder where [Status]!=5) and o.areaId=d.districtId";
            if (plateform != 0)
            {
                if (plateform == 1)
                {
                    sql += " and o.plateform<3 ";
                }
                else if (plateform == 2)
                {
                    sql += " and o.plateform=3 ";
                }
            }
            sql += " group by d.districtId";

            List<SelectGetAreaData> objList = BllBBCCommon.SelectEntityBySql<SelectGetAreaData>("orders", sql); ;


            List<EMapDataModel> list = new List<EMapDataModel>();

            foreach (SDistrict district in districtList)
            {
                bool flag = false;
                string districtName = district.DistrictName;
                long districtIdTemp = district.DistrictID;
                foreach (SelectGetAreaData obj in objList)
                {
                    long districtId = obj.districtId;
                    decimal value = obj.total;
                    if (districtIdTemp == districtId)
                    {
                        EMapDataModel model = new EMapDataModel();
                        model.name = districtName;
                        model.value = value;

                        list.Add(model);
                        flag = true;
                    }
                }
                if (!flag)
                {
                    EMapDataModel model = new EMapDataModel();
                    model.name = districtName;
                    model.value = 0;

                    list.Add(model);
                }
            }


            result.Status = 200;
            result.Body = list;

            return Json(result, JsonRequestBehavior.AllowGet);
        }

        private class SelectGetAreaData
        {
            public long districtId { get; set; }
            public decimal total { get; set; }
        }

        #endregion

    }
}